from openpyxl import load_workbook

from quant import data_utils
from quant.match import Match
import pandas as pd
from datetime import datetime, timedelta

DATA_FILE = "../data/20241230.csv"
HALF_CONFIG = "../data/half_20241230.xlsx"
DAYS = 365
COEF = 1
NOT_BETTING = 99
VERY_BIG_RATIO = 999

def read_matches(dt):
    base_df = pd.read_csv(DATA_FILE, encoding="UTF-8",
                          usecols=["场次", "比赛时间", "联赛", "主客场", "赛果", "让球赛果","半全场赔率",
                                   "胜", "平", "负",
                                   "胜临", "平临", "负临",
                                   "让胜", "让平", "让负",
                                   "让胜临", "让平临", "让负临",
                                   "让球", "总进球",
                                   "0临", "1临", "2临", "3临", "4临", "5临", "6临", "7临", "总进球赔率", "平平",
                                   "平平临", "半全场"],
                          header=0,
                          low_memory=False
                          )
    # base_df = base_df.dropna()
    base_df['比赛时间'] = pd.to_datetime(base_df['比赛时间'])

    df = base_df
    df = df[(df['比赛时间'] == dt)]
    # df['赛果'] = df['赛果'].apply(lambda x: 3 if x == "胜" else x)
    # df['赛果'] = df['赛果'].apply(lambda x: 1 if x == "平" else x)
    # df['赛果'] = df['赛果'].apply(lambda x: 0 if x == "负" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 3 if x == "胜" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 1 if x == "平" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 0 if x == "负" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 3 if x == "让胜" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 1 if x == "让平" else x)
    # df['让球赛果'] = df['让球赛果'].apply(lambda x: 0 if x == "让负" else x)

    matches = {}
    for index, row in df.iterrows():
        match = Match()
        match.no = row['场次']
        match.league = row['联赛']
        match.home_team = row['主客场'].split("VS")[0]
        match.away_team = row['主客场'].split("VS")[1]
        match.rangqiu = row['让球']
        match.win = float(row['胜'])
        match.draw = float(row['平'])
        match.lose = float(row['负'])
        match.rangqiu_win = row['让胜']
        match.rangqiu_draw = row['让平']
        match.rangqiu_lose = row['让负']
        match.result = row['赛果']
        match.rangqiu_result = row['让球赛果']
        match.goal_0 = row['0临']
        match.goal_1 = row['1临']
        match.goal_2 = row['2临']
        match.goal_3 = row['3临']
        match.goal_4 = row['4临']
        match.goal_5 = row['5临']
        match.goal_6 = row['6临']
        match.goal_7 = row['7临']
        match.goal_result = row['总进球']
        match.goal_ratio = row['总进球赔率']
        match.dd = row['平平临']
        match.half_result = row['半全场']
        match.half_coef = row['半全场赔率']
        match.game_date = dt
        matches[match.no] = match
    return matches

def predict_half():
    day = 0
    matches = data_utils.read_odds(day)
    half_ratio = data_utils.read_half_ratios(day)
    home_conditions,away_conditions = read_excel_config()

    options = []
    for m in matches.values():
        win = float(m['win'])
        lose = float(m['lose'])
        half_result = half_ratio[m["no"]][9]
        print(f"{m['no']} {m['home_team']} vs {m['away_team']} {m['win']} {m['draw']} {m['lose']}")
        for c in home_conditions:
            if c[0] <= win <= c[1]:
                if (half_result=="待开奖"):
                    hit_result= "待开奖"
                else:
                    if half_result==c[2]:
                        hit_result = "中"
                    else:
                        hit_result = "不中"
                options.append(
                    {"no": f"{m['no']} {m['home_team']} vs {m['away_team']} {m['win']} {m['draw']} {m['lose']}",
                     "option": c[2], "win_percent": c[3],"result":hit_result})

        for c in away_conditions:
            if c[0] <= lose <= c[1]:
                if (half_result=="待开奖"):
                    hit_result= "待开奖"
                else:
                    if half_result==c[2]:
                        hit_result = "中"
                    else:
                        hit_result = "不中"
                options.append(
                    {"no": f"{m['no']} {m['home_team']} vs {m['away_team']} {m['win']} {m['draw']} {m['lose']}",
                     "option": c[2], "win_percent": c[3],"result":hit_result})



    for option in options:
        print(f"推荐半全场 {option['no']} {option['option']} {option['win_percent']} {option['result']}")

def backtest_half():
    count = 1
    period = 20
    start_date = "2024-10-01"
    total_bet = 0
    total_win = 0
    bet_count = 0
    win_count = 0

    for i in range(0, period, 1):
        d = datetime.strptime(start_date, "%Y-%m-%d")
        current_date = (d + timedelta(days=i)).strftime("%Y-%m-%d")
        matches = read_matches(current_date)
        home_conditions, away_conditions = read_excel_config()
        options = []

        for m in matches.values():
            option = None
            win = m.win
            lose = m.lose
            half_result = m.half_result
            hit_result = "待开奖"
            # print(f"{m.no} {m.home_team} vs {m.away_team} {m.win} {m.draw} {m.lose}")
            for c in home_conditions:
                if c[0] < win <= c[1]:
                    total_bet += 1
                    bet_count += 1
                    if (half_result == "待开奖"):
                        hit_result = "待开奖"
                    else:
                        if half_result == c[2]:
                            hit_result = "中"
                            win_count += 1
                            total_win += round(m.half_coef,2)
                        else:
                            hit_result = "不中"
                    option = {"no": f"{m.no} {m.win} {m.draw} {m.lose}",
                         "option": c[2], "win_percent": c[3], "result": hit_result}

            for c in away_conditions:
                if c[0] < lose <= c[1]:
                    total_bet += 1
                    bet_count += 1
                    if (half_result == "待开奖"):
                        hit_result = "待开奖"
                    else:
                        if half_result == c[2]:
                            hit_result = "中"
                            win_count += 1
                            total_win += round(m.half_coef)
                        else:
                            hit_result = "不中"
                    option = {"no": f"{m.no} {m.win} {m.draw} {m.lose}",
                         "option": c[2], "win_percent": c[3], "result": hit_result}

            if option is not None:
                options.append(option)
                print(f"推荐半全场 {option['no']} {option['option']} {option['win_percent']} {option['result']} 下注:{total_bet} 赢:{total_win}|{win_count} 利:{total_win-total_bet}")



def read_excel_config():
    home_conditions = []
    away_conditions = []

    # 加载Excel文件
    wb = load_workbook(filename=HALF_CONFIG)

    # 选择工作表
    sheet = wb['Sheet7']

    row_num = 0
    # 遍历工作表中的行
    for row in sheet.iter_rows(values_only=True):
        row_num += 1
        if row_num>1:
            half = row[0]
            home_or_away=row[3][:2]
            start_coef = float(row[3].split("-")[0][2:])
            end_coef = float(row[3].split("-")[1])
            wins_percent = str(round(row[6]*100,2))+"%"
            if(home_or_away=="主胜"):
                home_conditions.append([start_coef,end_coef,half,wins_percent])
            else:
                away_conditions.append([start_coef, end_coef, half, wins_percent])
    return home_conditions,away_conditions

if __name__ == '__main__':
    # backtest_half()
    predict_half()
